package net.oschina.arvin.sqlbuilder;

import net.oschina.arvin.sqlbuilder.utils.AssertUtil;
import net.oschina.arvin.sqlbuilder.utils.Util;

/**
 * Where 语句构造器
 *
 * @author Arvin
 * @time 2017/11/16 15:56
 */
public class WhereBuilder extends AbstractQueryBuilder<WhereBuilder> {

    /** SQL 构造 */
    private StringBuilder whereBuilder = new StringBuilder();
    /** 条件数量 */
    private int conditionCount = 0;

    /** 上级Builder */
    private WhereBuilder parentBuilder = null;

    public WhereBuilder(Class<?> modelType) {
        super(modelType, SqlBuilderType.WHERE);
    }

    @Override
    public String buildSql() {
        String sql = build();
        AssertUtil.assertNotBlank(sql, "构建的SQL为空");
        setSql(sql);
        logUsedSql(sql);
        return sql;
    }

    @Override
    protected String build() {
        if (whereBuilder.length() > 0) {
            String sql = whereBuilder.toString();
            if (null == parentBuilder && !sql.matches("(?i) *WHERE.*")) {
                return " WHERE " + whereBuilder.toString();
            } else {
                return whereBuilder.toString();
            }
        }
        return "";
    }

    public WhereBuilder setParentBuilder(WhereBuilder parentBuilder) {
        this.parentBuilder = parentBuilder;
        return this;
    }

    /**
     * 返回条件数量
     */
    public int conditionCount() {
        return conditionCount;
    }

    /**
     * 预先适配，如果长度超过一就加AND，同时条件数量自增1
     *
     * @return 返回whereBuilder
     */
    private StringBuilder preAdapter(boolean and) {
        if (whereBuilder.length() > 1) {
            if (and) {
                whereBuilder.append(" AND ");
            } else {
                whereBuilder.append(" OR ");
            }
        }
        ++conditionCount;
        return whereBuilder;
    }

    /**
     * 等值比较
     *
     * @param and        是否使用and连接
     * @param columnName 数据库字段
     * @param value      比较的值
     */
    public WhereBuilder equal(boolean and, String columnName, Object value) {
        checkColumnName(columnName);
        preAdapter(and).append(Util.wrapColumnName(columnName)).append("=?");
        addParamByColumnName(columnName, value);
        return this;
    }

    public WhereBuilder equalByModelFieldName(boolean and, String modelFieldName, Object value) {
        checkModelFieldName(modelFieldName);
        preAdapter(and).append(getWrapColumnNameByModelModelFieldName(modelFieldName)).append("=?");
        addParamByModelFieldName(modelFieldName, value);
        return this;
    }

    /**
     * 使用 And 连接等值比较
     *
     * @param columnName 数据库字段
     * @param value      比较的值
     */
    public WhereBuilder andEqual(String columnName, Object value) {
        return equal(true, columnName, value);
    }

    public WhereBuilder andEqualByModelFieldName(String modelFieldName, Object value) {
        return equalByModelFieldName(true, modelFieldName, value);
    }

    /**
     * 使用 OR 连接等值比较
     *
     * @param columnName 数据库字段
     * @param value      比较的值
     */
    public WhereBuilder orEqual(String columnName, Object value) {
        return equal(false, columnName, value);
    }

    public WhereBuilder orEqualByModelFieldName(String modelFieldName, Object value) {
        return equalByModelFieldName(false, modelFieldName, value);
    }

    /**
     * 不等值比较
     *
     * @param and        是否使用and连接
     * @param columnName 数据库字段
     * @param value      值
     */
    public WhereBuilder notEqual(boolean and, String columnName, Object value) {
        checkColumnName(columnName);
        preAdapter(and).append(Util.wrapColumnName(columnName)).append("!=?");
        addParamByColumnName(columnName, value);
        return this;
    }

    public WhereBuilder notEqualByModelFieldName(boolean and, String modelFieldName, Object value) {
        checkModelFieldName(modelFieldName);
        preAdapter(and).append(getWrapColumnNameByModelModelFieldName(modelFieldName)).append("!=?");
        addParamByModelFieldName(modelFieldName, value);
        return this;
    }

    /**
     * 使用 And 连接不等值比较
     *
     * @param columnName 数据库字段
     * @param value      比较的值
     */
    public WhereBuilder andNotEqual(String columnName, Object value) {
        return notEqual(true, columnName, value);
    }

    public WhereBuilder andNotEqualByModelFieldName(String modelFieldName, Object value) {
        return notEqualByModelFieldName(true, modelFieldName, value);
    }

    /**
     * 使用 OR 连接不等值比较
     *
     * @param columnName 数据库字段
     * @param value      比较的值
     */
    public WhereBuilder orNotEqual(String columnName, Object value) {
        return notEqual(false, columnName, value);
    }

    public WhereBuilder orNotEqualByModelFieldName(String modelFieldName, Object value) {
        return notEqualByModelFieldName(false, modelFieldName, value);
    }

    /**
     * Like 查询
     *
     * @param and        是否使用and连接
     * @param columnName 数据库字段名称
     * @param value      值, 不需要带%
     */
    public WhereBuilder like(boolean and, String columnName, String value) {
        checkColumnName(columnName);
        preAdapter(and).append(Util.wrapColumnName(columnName)).append(" LIKE CONCAT('%',?,'%')");
        addParamByColumnName(columnName, value);
        return this;
    }

    public WhereBuilder likeByModelFieldName(boolean and, String modelFieldName, String value) {
        checkModelFieldName(modelFieldName);
        preAdapter(and).append(getWrapColumnNameByModelModelFieldName(modelFieldName)).append(" LIKE CONCAT('%',?,'%')");
        addParamByModelFieldName(modelFieldName, value);
        return this;
    }

    /**
     * 使用 AND 连接的 Like 查询
     *
     * @param columnName 数据库字段名称
     * @param value      值, 不需要带%
     */
    public WhereBuilder andLike(String columnName, String value) {
        return like(true, columnName, value);
    }

    public WhereBuilder andLikeByModelFieldName(String modelFieldName, String value) {
        return likeByModelFieldName(true, modelFieldName, value);
    }

    /**
     * 使用 OR 连接的 Like 查询
     *
     * @param columnName 数据库字段名称
     * @param value      值, 不需要带%
     */
    public WhereBuilder orLike(String columnName, String value) {
        return like(false, columnName, value);
    }

    public WhereBuilder orLikeByModelFieldName(String modelFieldName, String value) {
        return likeByModelFieldName(false, modelFieldName, value);
    }

    /**
     * Between 语句
     *
     * @param and        是否使用and连接
     * @param columnName 数据库字段名称
     * @param first      第一个
     * @param second     第二个
     */
    private WhereBuilder between(boolean and, String columnName, Object first, Object second) {

        if (null == first && null == second) {
            return this;
        }

        checkColumnName(columnName);

        preAdapter(and);
        columnName = Util.wrapColumnName(columnName);

        if (null == first) {
            whereBuilder.append(columnName).append("<=?");
            addParamByColumnName(columnName, second);
            return this;
        }

        if (null == second) {
            whereBuilder.append(columnName).append(">=?");
            addParamByColumnName(columnName, first);
            return this;
        }

        whereBuilder.append(columnName).append(" BETWEEN ? AND ?");
        addParamByColumnName(columnName, first);
        addParamByColumnName(columnName, second);
        return this;
    }

    private WhereBuilder betweenByModelFieldName(boolean and, String modelFieldName, Object first, Object second) {
        checkModelFieldName(modelFieldName);
        return between(and, getWrapColumnNameByModelModelFieldName(modelFieldName), first, second);
    }

    /**
     * 使用 AND 连接的 Between 语句
     *
     * @param columnName 数据库字段名称
     * @param first      第一个
     * @param second     第二个
     */
    public WhereBuilder andBetween(String columnName, Object first, Object second) {
        return between(true, columnName, first, second);
    }

    public WhereBuilder andBetweenByModelFieldName(String modelFieldName, Object first, Object second) {
        return betweenByModelFieldName(true, modelFieldName, first, second);
    }

    /**
     * 使用 OR 连接的 Between 语句
     *
     * @param columnName 数据库字段名称
     * @param first      第一个
     * @param second     第二个
     */
    public WhereBuilder orBetween(String columnName, Object first, Object second) {
        return between(false, columnName, first, second);
    }

    public WhereBuilder orBetweenByModelFieldName(String modelFieldName, Object first, Object second) {
        return betweenByModelFieldName(false, modelFieldName, first, second);
    }

    /**
     * 添加子句
     *
     * @param and      是否使用and连接
     * @param subWhere 子句
     */
    public WhereBuilder subWhere(boolean and, WhereBuilder subWhere) {
        if (null == subWhere || this == subWhere) {
            return this;
        }
        if (subWhere != this) {
            subWhere.setParentBuilder(this);
        }
        if (this.whereBuilder.length() < 1) {
            throw new IllegalArgumentException("Current Where Builder is null!");
        }
        preAdapter(and);
        String sql = subWhere.getSql().replaceFirst(" *WHERE *", "").replaceFirst(" *AND *$", "").replaceFirst(" *OR *$", "");
        // 超过一个条件，需要加括号
        if (subWhere.conditionCount() > 1) {
            whereBuilder.append("(").append(sql).append(")");
        } else { // 不需要加括号
            whereBuilder.append(sql);
        }
        // 添加参数
        addListSqlParams(subWhere.getSqlParamList());
        return this;
    }

    /**
     * 使用AND 连接的子查询WHERE
     *
     * @param subWhere 子where
     */
    public WhereBuilder andSubWhere(WhereBuilder subWhere) {
        return subWhere(true, subWhere);
    }

    /**
     * 使用 OR 连接的子查询WHERE
     *
     * @param subWhere 子where
     */
    public WhereBuilder orSubWhere(WhereBuilder subWhere) {
        return subWhere(false, subWhere);
    }

    /**
     * IN 语句查询
     *
     * @param and        是否使用AND查询
     * @param in         是否使用IN，还是使用NOT IN
     * @param columnName 数据库字段
     * @param subSelect  子查询
     * @param params     参数
     */
    private WhereBuilder inOrNot(boolean and, boolean in, String columnName, String subSelect, Object... params) {
        checkColumnName(columnName);
        preAdapter(and);
        whereBuilder.append(Util.wrapColumnName(columnName)).append(in ? "" : " NOT").append(" IN (").append(subSelect).append(")");
        addParamsByValues(params);
        return this;
    }

    private WhereBuilder inOrNotByModelFieldName(boolean and, boolean in, String modelFieldName, String subSelect, Object... params) {
        checkModelFieldName(modelFieldName);
        preAdapter(and);
        whereBuilder.append(getWrapColumnNameByModelModelFieldName(modelFieldName)).append(in ? "" : " NOT").append(" IN (").append(subSelect).append(")");
        addParamsByValues(params);
        return this;
    }

    /**
     * 使用 AND 连接 IN 查询
     *
     * @param columnName 数据库字段
     * @param subSelect  子查询
     * @param params     参数列表
     */
    public WhereBuilder andIn(String columnName, String subSelect, Object... params) {
        return inOrNot(true, true, columnName, subSelect, params);
    }

    public WhereBuilder andInByModelFieldName(String modelFieldName, String subSelect, Object... params) {
        return inOrNotByModelFieldName(true, true, modelFieldName, subSelect, params);
    }

    /**
     * 使用 OR 连接 IN 查询
     *
     * @param columnName 数据库字段
     * @param subSelect  子查询
     * @param params     参数列表
     */
    public WhereBuilder orIn(String columnName, String subSelect, Object... params) {
        return inOrNot(false, true, columnName, subSelect, params);
    }

    public WhereBuilder orInByModelFieldName(String modelFieldName, String subSelect, Object... params) {
        return inOrNotByModelFieldName(false, true, modelFieldName, subSelect, params);
    }

    /**
     * 使用 AND 连接 NOT IN 查询
     *
     * @param columnName 数据库字段
     * @param subSelect  子查询
     * @param params     参数列表
     */
    public WhereBuilder andNotIn(String columnName, String subSelect, Object... params) {
        return inOrNot(true, false, columnName, subSelect, params);
    }

    public WhereBuilder andNotInByModelFieldName(String modelFieldName, String subSelect, Object... params) {
        return inOrNotByModelFieldName(true, false, modelFieldName, subSelect, params);
    }

    /**
     * 使用 OR 连接 NOT IN 查询
     *
     * @param columnName 数据库字段
     * @param subSelect  子查询
     * @param params     参数列表
     */
    public WhereBuilder orNotIn(String columnName, String subSelect, Object... params) {
        return inOrNot(true, false, columnName, subSelect, params);
    }

    public WhereBuilder orNotInByModelFieldName(String modelFieldName, String subSelect, Object... params) {
        return inOrNotByModelFieldName(false, false, modelFieldName, subSelect, params);
    }

    /**
     * 连接是否是 IS NULL 还是 IS NOT NULL
     *
     * @param and        是否使用AND连接
     * @param isNull     是否IS NULL
     * @param columnName 字段
     */
    private WhereBuilder nullOrNot(boolean and, boolean isNull, String columnName) {
        checkColumnName(columnName);
        preAdapter(and);
        whereBuilder.append(Util.wrapColumnName(columnName)).append(isNull ? " IS NULL " : " IS NOT NULL");
        return this;
    }

    private WhereBuilder nullOrNotByModelFieldName(boolean and, boolean isNull, String modelFieldName) {
        checkModelFieldName(modelFieldName);
        preAdapter(and);
        whereBuilder.append(getWrapColumnNameByModelModelFieldName(modelFieldName)).append(isNull ? " IS NULL " : " IS NOT NULL");
        return this;
    }

    /**
     * 使用 AND 连接 IS NULL
     *
     * @param columnName 数据库字段
     */
    public WhereBuilder andIsNull(String columnName) {
        return nullOrNot(true, true, columnName);
    }

    public WhereBuilder andIsNullByModelFieldName(String modelFieldName) {
        return nullOrNotByModelFieldName(true, true, modelFieldName);
    }

    /**
     * 使用 OR 连接 IS NULL
     *
     * @param columnName 数据库字段
     */
    public WhereBuilder orIsNull(String columnName) {
        return nullOrNot(false, true, columnName);
    }

    public WhereBuilder orIsNullByModelFieldName(String modelFieldName) {
        return nullOrNotByModelFieldName(false, true, modelFieldName);
    }

    /**
     * 使用 AND 连接 IS NOT NULL
     *
     * @param columnName 数据库字段
     */
    public WhereBuilder andIsNotNull(String columnName) {
        return nullOrNot(true, false, columnName);
    }

    public WhereBuilder andIsNotNullByModelFieldName(String modelFieldName) {
        return nullOrNotByModelFieldName(true, false, modelFieldName);
    }

    /**
     * 使用 OR 连接 IS NOT NULL
     *
     * @param columnName 数据库字段
     */
    public WhereBuilder orIsNotNull(String columnName) {
        return nullOrNot(false, false, columnName);
    }

    public WhereBuilder orIsNotNullByModelFieldName(String modelFieldName) {
        return nullOrNotByModelFieldName(false, false, modelFieldName);
    }

    /**
     * 大于、大于等于，小于，小于等于
     *
     * @param and        是否AND连接
     * @param lessThan   小于
     * @param equal      是否需要等于
     * @param columnName 数据库字段
     * @param value      参数
     */
    private WhereBuilder compareOrCompareEqual(boolean and, boolean lessThan, boolean equal, String columnName, Object value) {
        checkColumnName(columnName);
        preAdapter(and);
        whereBuilder.append(Util.wrapColumnName(columnName)).append(lessThan ? "<" : ">").append(equal ? "=?" : "?");
        addParamByColumnName(columnName, value);
        return this;
    }

    private WhereBuilder compareOrCompareEqualByModelFieldName(boolean and, boolean lessThan, boolean equal, String modelFieldName, Object value) {
        checkModelFieldName(modelFieldName);
        preAdapter(and);
        whereBuilder.append(getWrapColumnNameByModelModelFieldName(modelFieldName)).append(lessThan ? "<" : ">").append(equal ? "=?" : "?");
        addParamByModelFieldName(modelFieldName, value);
        return this;
    }

    /**
     * AND 连接大于
     */
    public WhereBuilder andGreaterThan(String columnName, Object value) {
        return compareOrCompareEqual(true, false, false, columnName, value);
    }

    public WhereBuilder andGreaterThanByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(true, false, false, modelFieldName, value);
    }

    /**
     * AND 连接大于等于
     */
    public WhereBuilder andGreaterThanEqual(String columnName, Object value) {
        return compareOrCompareEqual(true, false, true, columnName, value);
    }

    public WhereBuilder andGreaterThanEqualByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(true, false, true, modelFieldName, value);
    }

    /**
     * OR 连接大于
     */
    public WhereBuilder orGreaterThan(String columnName, Object value) {
        return compareOrCompareEqual(false, false, false, columnName, value);
    }

    public WhereBuilder orGreaterThanByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(false, false, false, modelFieldName, value);
    }

    /**
     * OR 连接大于等于
     */
    public WhereBuilder orGreaterThanEqual(String columnName, Object value) {
        return compareOrCompareEqual(false, false, true, columnName, value);
    }

    public WhereBuilder orGreaterThanEqualByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(false, false, true, modelFieldName, value);
    }

    /**
     * AND 连接大于
     */
    public WhereBuilder andLessThan(String columnName, Object value) {
        return compareOrCompareEqual(true, true, false, columnName, value);
    }

    public WhereBuilder andLessThanByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(true, true, false, modelFieldName, value);
    }

    /**
     * AND 连接大于等于
     */
    public WhereBuilder andLessThanEqual(String columnName, Object value) {
        return compareOrCompareEqual(true, true, true, columnName, value);
    }

    public WhereBuilder andLessThanEqualByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(true, true, true, modelFieldName, value);
    }

    /**
     * OR 连接大于
     */
    public WhereBuilder orLessThan(String columnName, Object value) {
        return compareOrCompareEqual(false, true, false, columnName, value);
    }

    public WhereBuilder orLessThanByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(false, true, false, modelFieldName, value);
    }

    /**
     * OR 连接大于等于
     */
    public WhereBuilder orLessThanEqual(String columnName, Object value) {
        return compareOrCompareEqual(false, true, true, columnName, value);
    }

    public WhereBuilder orLessThanEqualByModelFieldName(String modelFieldName, Object value) {
        return compareOrCompareEqualByModelFieldName(false, true, true, modelFieldName, value);
    }

    /**
     * 自定义构造
     *
     * @param and    是否使用AND连接
     * @param where  where子句
     * @param params 参数
     */
    private WhereBuilder append(boolean and, String where, Object... params) {
        preAdapter(and);
        whereBuilder.append(Util.wrapBracketPair(where));
        addParamsByValues(params);
        return this;
    }

    /**
     * 使用AND连接自定义Where
     *
     * @param whereExpress where表达式
     * @param params       参数
     */
    public WhereBuilder andAppend(String whereExpress, Object... params) {
        return append(true, whereExpress, params);
    }

    /**
     * 使用 OR 连接自定义Where
     *
     * @param whereExpress where表达式
     * @param params       参数
     */
    public WhereBuilder orAppend(String whereExpress, Object... params) {
        return append(false, whereExpress, params);
    }

    /**
     * 构造存在或不存在where子句
     *
     * @param and          是否使用AND连接
     * @param exists       是否存在
     * @param subSelectSql 子查询语句
     * @param params       参数
     */
    private WhereBuilder existsOrNot(boolean and, boolean exists, String subSelectSql, Object... params) {
        preAdapter(and);
        whereBuilder.append(exists ? "" : "NOT ").append("EXISTS (").append(subSelectSql).append(")");
        addParamsByValues(params);
        return this;
    }

    /**
     * 使用AND连接Exists子查询
     *
     * @param subSelect 子查询
     * @param params    参数
     */
    public WhereBuilder andExists(String subSelect, Object... params) {
        return existsOrNot(true, true, subSelect, params);
    }

    /**
     * 使用 OR 连接Exists子查询
     *
     * @param subSelect 子查询
     * @param params    参数
     */
    public WhereBuilder orExists(String subSelect, Object... params) {
        return existsOrNot(false, true, subSelect, params);
    }

    /**
     * 使用AND连接Not Exists子查询
     *
     * @param subSelect 子查询
     * @param params    参数
     */
    public WhereBuilder andNotExists(String subSelect, Object... params) {
        return existsOrNot(true, false, subSelect, params);
    }

    /**
     * 使用 OR 连接Not Exists子查询
     *
     * @param subSelect 子查询
     * @param params    参数
     */
    public WhereBuilder orNotExists(String subSelect, Object... params) {
        return existsOrNot(false, false, subSelect, params);
    }

    @Override
    protected WhereBuilder self() {
        return this;
    }
}
